import pymysql
import pymysqlpool
from sqlResultToStudentObj import toStudentObj
from config import config
# db = pymysql.connect(**config)
pool1 = pymysqlpool.ConnectionPool(size=2, maxsize=10, pre_create_num=2, name='pool1', **config)

def getUsers():
    try:
        conn = pool1.get_connection()
        conn.autocommit(True)
        cursor = conn.cursor()
        command = "SELECT name,role FROM User"
        print(command)
        cursor.execute(command)
        results = cursor.fetchall()

        users = []
        for data in results:
            users.append({'name': data[0], 'role':data[1]})
        return users
    except Exception as error:
        raise error
    finally:
        conn.close()

def editUser(name, role):
    try:
        conn = pool1.get_connection()
        conn.autocommit(True)
        cursor = conn.cursor()
        command = f"UPDATE User SET role = '{role}' WHERE name = '{name}'"
        print(command)
        cursor.execute(command)
        conn.commit()

        return {
            'name': name,
            'role': role,
        }
    except Exception as error:
        raise error
    finally:
        conn.close()

def deleteUser(name):
    try:
        conn = pool1.get_connection()
        conn.autocommit(True)
        cursor = conn.cursor()
        command = f"DELETE FROM User WHERE name = '{name}'"
        print(command)
        rows = cursor.execute(command)
        conn.commit()

        if(rows != 0):
            return "User record deleted successfully."
        else:
            raise Exception(500, "删除失败")
    except Exception as error:
        raise error
    finally:
        conn.close()

def getStudentNameId(name,id):
    try:
        conn = pool1.get_connection()
        conn.autocommit(True)
        cursor = conn.cursor()
        command = f"SELECT * FROM Student WHERE name LIKE '%{name}%' AND id = '{id}'"
        if(name == '' and id==''):
            command = "SELECT * FROM Student"
        else:
            if(name==''):
                command = f"SELECT * FROM Student WHERE id = '{id}'"
            if(id==''):
                command = f"SELECT * FROM Student WHERE name LIKE '%{name}%'"
        print(command)
        cursor.execute(command)
        results = cursor.fetchall()

        students = []
        for data in results:
            students.append(toStudentObj(data))
        return students
    except Exception as error:
        raise error
    finally:
        conn.close()
    
def getStudentCondition(condition):
    try:
        conn = pool1.get_connection()
        conn.autocommit(True)
        cursor = conn.cursor()

        command = f"SELECT * FROM Student WHERE {condition}"
        if(str(condition) == ""):
            command = "SELECT * FROM Student"
        print(command)
        cursor.execute(command)
        results = cursor.fetchall()

        students = []
        for data in results:
            students.append(toStudentObj(data))
        return students
    except Exception as error:
        raise error
    finally:
        conn.close()
    

def deleteStudentNameId(name, id):
    try:
        conn = pool1.get_connection()
        conn.autocommit(True)
        cursor = conn.cursor()
        command = f"DELETE FROM Student WHERE name = '{name}' AND id = '{id}'"
        print(command)
        rows = cursor.execute(command)

        conn.commit()

        if(rows != 0):
            return "Studen record deleted successfully."
        else:
            raise Exception(500, "删除失败")
    except Exception as error:
        conn.rollback()
        raise error
    finally:
        conn.close()
    

def addStudentNameId(name, id, chinese, math, english, physics, history, chemistry, biology, politics, geography):
    try:
        conn = pool1.get_connection()
        conn.autocommit(True)
        cursor = conn.cursor()
        command = """
        INSERT INTO Student (name, id, chinese, math, english, physics, history, chemistry, biology, politics, geography)
        VALUES ('{}', '{}', '{}','{}', '{}', '{}','{}', '{}', '{}','{}', '{}')
        """.format(name, id, chinese, math, english, physics,history,chemistry, biology, politics, geography)
        print(command)

        cursor.execute(command)

        conn.commit()

        return {
            "name": name,
            "id": id,
            "chinese": chinese,
            "math": math,
            "emglish": english,
            "physics": physics,
            "history": history,
            "chemistry": chemistry,
            "biology": biology,
            "polotics": politics,
            "geography": geography,
        }
    except Exception as error:
        raise error
    finally:
        conn.close()
    

def editStudentNameId(name, id, chinese = None, math = None, english= None, physics = None, history = None,chemistry = None, biology = None, politics = None, geography = None):
    try:
        conn = pool1.get_connection()
        conn.autocommit(True)
        cursor = conn.cursor()

        command = """
        UPDATE Student SET chinese = '{}', math = '{}', english = '{}', history = '{}', chemistry = '{}', biology = '{}', politics = '{}', geography = '{}' WHERE name = '{}' AND id = '{}'
        """.format( chinese , math , english, physics , history, chemistry , biology , politics , geography, name, id )
        print(command)

        if any([ chinese , math , english, physics , history, chemistry , biology , politics , geography ]):
            cursor.execute(command)
            conn.commit()

        return {
            "name": name,
            "id": id,
            "chinese": chinese,
            "math": math,
            "emglish": english,
            "physics": physics,
            "history": history,
            "chemistry": chemistry,
            "biology": biology,
            "polotics": politics,
            "geography": geography,
        }
    except Exception as error:
        raise error
    finally:
        conn.close()
    

def Login(name,password):
    try:
        conn = pool1.get_connection()
        conn.autocommit(True)
        cursor = conn.cursor()

        command = f"SELECT * FROM User WHERE name = '{name}' AND password = '{password}'"
        print(command)

        cursor.execute(command)
        result = cursor.fetchone()

        print(result)

        if result:
            return {
                "name": result[0],
                "role": result[2]
            }
        else :
            raise Exception(200, "用户名或密码错误")

    except Exception as error:
        raise error
    finally:
        conn.close()

def Register(name, password, role):
    try:
        conn = pool1.get_connection()
        conn.autocommit(True)
        cursor = conn.cursor()

        command = f"INSERT INTO User (name, password, role) VALUES ('{name}', '{password}', '{role}')"
        print(command)

        cursor.execute(command)
        conn.commit()

        return {
            "name": name,
            "role": role
        }

    except Exception as error:
        raise error
    finally:
        conn.close()
    
    
